﻿using AutoMapper;
using B.S.BaseData.ErrorCode;
using B.S.BaseData.Manage.Api.Read.Apilaction.Command.InventoryCommand;
using B.S.BaseData.Manage.Api.Read.DTO;
using B.S.BaseData.Manage.Domain.Entities;
using B.S.DataBase.Traceability.Infrastructure.Interface;
using Dapper;
using MediatR;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace B.S.BaseData.Manage.Api.Read.Apilaction.CommandHandler.InventoryCommandHandler
{
    public class InventoryQueryCommandHandler : IRequestHandler<InventoryQuserCommand, ApiReqult<ApIPageing<InventoryQuserDTO>>>
    {
        private readonly string connectionString;
        private readonly IBaseRepository<Inventory> repository;
        private readonly IBaseRepository<Stock_operation_batch> repository2;
        private readonly ILogger<InventoryQueryCommandHandler> logger;

        public InventoryQueryCommandHandler(string connectionString, IBaseRepository<Inventory> repository, IBaseRepository<Stock_operation_batch> repository2, ILogger<InventoryQueryCommandHandler> logger)
        {
            this.connectionString = connectionString;
            this.repository = repository;
            this.repository2 = repository2;
            this.logger = logger;
        }

        public async Task<ApiReqult<ApIPageing<InventoryQuserDTO>>> Handle(InventoryQuserCommand request, CancellationToken cancellationToken)
        {
            var result = new ApiReqult<ApIPageing<InventoryQuserDTO>>();
            try
            {
                // 参数验证
                if (request.PageIdex < 1) request.PageIdex = 1;
                if (request.PageSize < 1 || request.PageSize > 100)
                    request.PageSize = 10;

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync(cancellationToken);
                    var whereClauses = new List<string>();
                    var parameters = new DynamicParameters();

                    // 产品编号过滤
                    if (!string.IsNullOrEmpty(request.Product_id))
                    {
                        whereClauses.Add("a.Product_id = @Product_id");
                        parameters.Add("Product_id", request.Product_id);
                    }

                    // 产品名称模糊查询
                    if (!string.IsNullOrEmpty(request.Product_name))
                    {
                        whereClauses.Add("a.Product_name LIKE @Product_name");
                        parameters.Add("Product_name", $"%{request.Product_name}%");
                    }

                    // 仓库名称过滤
                    if (!string.IsNullOrEmpty(request.Warehouse_name))
                    {
                        whereClauses.Add("a.Warehouse_name = @Warehouse_name");
                        parameters.Add("Warehouse_name", request.Warehouse_name);
                    }

                    // 批次编号过滤
                    string batchFilter = "";
                    if (!string.IsNullOrEmpty(request.Batch_no))
                    {
                        batchFilter = " AND b.Batch_no = @Batch_no";
                        parameters.Add("Batch_no", request.Batch_no);
                    }

                    var whereClause = string.Join(" AND ", whereClauses);
                    if (!string.IsNullOrEmpty(whereClause)) whereClause = "WHERE " + whereClause;

                    // 计算总记录数
                    var countQuery = $@"
                        SELECT COUNT(*) 
                        FROM Inventory a
                        LEFT JOIN Stock_operation_batch b ON a.Product_id = b.Product_id {batchFilter}
                        {whereClause}";
                    var count = await connection.ExecuteScalarAsync<int>(countQuery, parameters);

                    // 分页查询
                    var offset = (request.PageIdex - 1) * request.PageSize;
                    parameters.Add("Offset", offset);
                    parameters.Add("PageSize", request.PageSize);

                    var pagedQuery = $@"
                        WITH RankedInventory AS (
                            SELECT 
                                a.inventoryId,
                                a.Product_id, a.Product_name, a.Spec, a.Category, a.Storage_unit,
                                a.Warehouse_id, a.Warehouse_name, a.Location_id, a.Location_name,
                                a.Location_type, a.Stock_quantity, a.upper_limit, a.lower_limit,
                                b.Id, b.Operation_date, b.In_quantity, b.Out_quantity, b.Batch_no, b.Remark,
                                ROW_NUMBER() OVER (PARTITION BY a.Product_id ORDER BY b.Operation_date DESC) AS Rank
                            FROM Inventory a
                            LEFT JOIN Stock_operation_batch b ON a.Product_id = b.Product_id {batchFilter}
                            {whereClause}
                        )
                        SELECT * FROM RankedInventory WHERE Rank = 1
                        ORDER BY Product_id
                        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

                    var list = (await connection.QueryAsync<InventoryQuserDTO>(pagedQuery, parameters)).ToList();
                    var model = new ApIPageing<InventoryQuserDTO>
                    {
                        TotalCount = count,
                        TotalPage = count == 0 ? 0 : (int)Math.Ceiling(count * 1.0 / request.PageSize),
                        List = list
                    };

                    result.Code = ApiEnum.查询成功;
                    result.Msg = "查询成功";
                    result.Data = model;
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "库存清单查询失败：{Message}", ex.Message);
                result.Code = ApiEnum.查询失败;
                result.Msg = "查询失败，请稍后重试";
            }
            return result;
        }
    }
}